#///////////////////////////////////////////////////////////////////////////////
# READ SECOND-BY-SECOND DATA FROM THE TED5000
#
# JOE KWIATKOWSKI, 2010/07/30
# http://joejk.com
#///////////////////////////////////////////////////////////////////////////////
import sqlite3
import urllib
import time 

#CONFIGURATION:
httpAddress = "http://71.139.198.250" #The URL of your device.

#CONNECT TO DATABASE.
try:
    cxn=sqlite3.connect('ted5000.sqlite')
    cur = cxn.cursor()
except: 
    print 'Ooops - cannot connect to db.'        

#CREATE TABLE, IF NECESSARY.
try: 
    sql='CREATE TABLE data(dateTime  DATETIME ' + \
                'PRIMARY KEY, kW FLOAT, V FLOAT)';
    print sql
    cur.execute(sql)
    cxn.commit()
except: 
    print 'Table exists.'


#REPEATEDLY DOWNLOAD DATA AND STORE INTO DATABASE.
while True:
    #Count existing records:
    countOld = int(cur.execute('SELECT COUNT(*) FROM data').fetchone()[0])
    #Retrieve data:
    data = urllib.urlopen(httpAddress + "/history/secondhistory.csv?MTU=0&INDEX=0&U=1");
    data = data.readlines();
    #Write data:
    for record in data[1:]: #(Discard labels in 1st record)
        record.strip()
        datum=record.split(',')
        #Reformat the date:
        _date, _time = datum[1].split()
        m, d, y = _date.split('/')
        _date = '-'.join([y,m,d])
        dateTime = ' '.join([_date, _time])
        #Insert, ignoring any duplicated records:
        sqlText = 'INSERT OR IGNORE INTO data VALUES(datetime("%s"), %s, %s)' %\
                (dateTime, datum[2], datum[3])
        cur.execute(sqlText)
    cxn.commit()
    #Calculate number of new records:
    countNew = int(cur.execute('SELECT COUNT(*) FROM data').fetchone()[0])
    output = 'Added %d records at time ' % (countNew - countOld) +\
             str(time.strftime("%a, %d %b %Y %H:%M:%S", time.localtime()))
    print output
    #Sleep for 45 minutes (TED 5000 stores an hour's worth of seconds data).
    time.sleep(2700)

#CLOSE NICELY.
cxn.close()
#///////////////////////////////////////////////////////////////////////////////
